﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using Infrastructure.Utilities;

namespace LuckyDraw.WinUI
{
	public static class DB
	{
		public static void ResetResult(string prizeType)
		{
			if (prizeType.IsNullOrEmpty())
			{
				return;
			}

			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd = conn.CreateCommand();
				cmd.CommandText = "update DrawPeople set Result = null where Result = '{0}'".Fmt(prizeType);
				cmd.ExecuteNonQuery();
			}
		}

		public static List<Strategy> SelectStrategy()
		{
			List<Strategy> list = new List<Strategy>();
			using (SQLiteConnection conn = DB.OpenDB())
			{
				SQLiteCommand cmd = conn.CreateCommand();
				cmd.CommandText = Strategy.SelectStrategy_SQL;
				SQLiteDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					Strategy strategy = new Strategy()
					{
						Id = reader.GetInt32(0),
						Name = reader.GetString(1),
						Number = reader.GetInt32(2)
					};
					list.Add(strategy);
				}
			}
			return list;
		}

		public static void SaveStrategy(List<Strategy> strategyList)
		{
			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd2 = conn.CreateCommand();
				cmd2.CommandText = "delete from Strategy";
				cmd2.ExecuteNonQuery();

				foreach (Strategy strategy in strategyList)
				{
					SQLiteCommand cmd = conn.CreateCommand();
					cmd.CommandText = Strategy.InsertStrategy_SQL.Fmt(strategy.Id, strategy.Name, strategy.Number);
					cmd.ExecuteNonQuery();
				}
			}
		}

		public static void Absent(string couponNo)
		{
			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd = conn.CreateCommand();
				cmd.CommandText = DrawPeople.UpdateResult_SQL.Fmt("缺席", couponNo);
				cmd.ExecuteNonQuery();
			}
		}

		public static void InsertDrawPeople(List<DrawPeople> entities)
		{
			if (entities == null)
			{
				return;
			}

			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd2 = conn.CreateCommand();
				cmd2.CommandText = "delete from DrawPeople";
				cmd2.ExecuteNonQuery();

				foreach (DrawPeople entity in entities)
				{
					SQLiteCommand cmd = conn.CreateCommand();
					cmd.CommandText = "INSERT INTO DrawPeople(Id,Name,Mobile,Company,CouponNo,PrizeType1,PrizeType2,PrizeType3,PrizeType4,PrizeType5) VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')"
						.Fmt(entity.Id, entity.Name, entity.Mobile, entity.Company, entity.CouponNo.Trim(), entity.PrizeType1, entity.PrizeType2, entity.PrizeType3, entity.PrizeType4, entity.PrizeType5);
					cmd.ExecuteNonQuery();
				}
			}
		}

		public static List<string> SelectPrizeType()
		{
			List<string> list = new List<string>();
			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd = conn.CreateCommand();
				cmd.CommandText = DrawPeople.SelectPrizeType_SQL;

				SQLiteDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					object obj = reader[0];
					if (obj == null || obj.ToString().IsNullOrEmpty())
					{
						continue;
					}

					list.Add(obj.ToString());
				}
			}
			return list;
		}

		public static List<DrawPeople> SelectDrawPeople()
		{
			List<DrawPeople> list = new List<DrawPeople>();
			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd = conn.CreateCommand();
				cmd.CommandText = "select * from DrawPeople";
				SQLiteDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					DrawPeople entity = new DrawPeople();
					entity.Id = reader.GetFieldValue<double>(0);
					entity.Name = reader.GetFieldValue<string>(1);
					entity.Mobile = reader.GetFieldValue<string>(2);
					entity.Company = reader.GetFieldValue<string>(3);
					entity.CouponNo = reader.GetFieldValue<string>(4);
					entity.PrizeType1 = reader.GetFieldValue<string>(5);
					if (!reader.IsDBNull(6))
					{
						entity.PrizeType2 = reader.GetFieldValue<string>(6);
					}
					if (!reader.IsDBNull(7))
					{
						entity.PrizeType3 = reader.GetFieldValue<string>(7);
					}
					if (!reader.IsDBNull(8))
					{
						entity.PrizeType4 = reader.GetFieldValue<string>(8);
					}
					if (!reader.IsDBNull(9))
					{
						entity.PrizeType5 = reader.GetFieldValue<string>(9);
					}
					if (!reader.IsDBNull(10))
					{
						entity.Result = reader.GetFieldValue<string>(10);
					}

					if (!entity.CouponNo.IsNullOrEmpty())
					{
						list.Add(entity);
					}
				}
			}
			return list;
		}

		public static void UpdateResult(DrawPeople winning)
		{
			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd = conn.CreateCommand();
				cmd.CommandText = DrawPeople.UpdateResult_SQL.Fmt(winning.Result, winning.CouponNo);
				cmd.ExecuteNonQuery();
			}
		}

		public static void CreateDB()
		{
			if (!File.Exists("LuckyDraw.db"))
			{
				SQLiteConnection.CreateFile("LuckyDraw.db");

				using (SQLiteConnection conn = OpenDB())
				{
					SQLiteCommand cmd1 = conn.CreateCommand();
					cmd1.CommandText = DrawPeople.CreateTable_SQL;
					cmd1.ExecuteNonQuery();

					SQLiteCommand cmd2 = conn.CreateCommand();
					cmd2.CommandText = Strategy.CreateTable_SQL;
					cmd2.ExecuteNonQuery();
				}
			}
		}

		private static void ExecuteNonQuery(string sql)
		{
			using (SQLiteConnection conn = OpenDB())
			{
				SQLiteCommand cmd = conn.CreateCommand();
				cmd.CommandText = sql;
				cmd.ExecuteNonQuery();
			}
		}

		private static SQLiteConnection OpenDB()
		{
			SQLiteConnection conn = new SQLiteConnection("Data Source=LuckyDraw.db;Version=3;");
			conn.Open();
			return conn;
		}
	}
}
